﻿Imports System.Data.SqlClient

''' <summary>
''' This class implements a MSSQL collector.
''' </summary>
''' <remarks>
''' The purpose of the MSSQL collector is to run queries against an MSSQL
''' server and output its contents to the system info report. The queries
''' are designed to include information about the MSSQL server instance, 
''' about the scanned database and about individual tables.
''' </remarks>
Public Class MSSQLCollector
    Implements ICollector

    ''' <summary>
    ''' List of writers passed when Startup() is called.
    ''' </summary>
    ''' <remarks></remarks>
    Protected m_writers As List(Of Writer.IWriter)

    ''' <summary>
    ''' List of MSSQL entries.
    ''' </summary>
    ''' <remarks></remarks>
    Protected m_entries As List(Of MSSQLEntry)

    ''' <summary>
    ''' This holds an error description.
    ''' </summary>
    ''' <remarks></remarks>
    Protected m_error As String

    Public Sub Cleanup() Implements ICollector.Cleanup

        'Nothing to do

    End Sub

    Public Sub Run() Implements ICollector.Run

        'Write our category name.
        Writer.WritersHelper.WriteCategory("MSSQL Information", m_writers)

        'If an error occurred while reading the configuration file, write
        'it out and stop here.
        If m_error <> "" Then
            Writer.WritersHelper.WriteCategoryError(m_error, m_writers)
        Else
            'Scan all mssql entries.
            For Each sql As MSSQLEntry In m_entries
                Try
                    'Write the database server and database name.
                    Writer.WritersHelper.WriteSection(sql.Server + ", " + sql.Database, m_writers)
                    'Process this item.
                    Try
                        'Get a connection to the database.
                        Dim sqlCon As New SqlConnection(sql.getConnectionString)
                        sqlCon.Open()

                        'Run the query.
                        Dim ds As New DataSet
                        Dim sqlDA As New SqlDataAdapter(sql.SQLQuery, sqlCon)
                        sqlDA.Fill(ds)
                        sqlDA.Dispose()
                        sqlDA = Nothing
                        sqlCon.Close()
                        sqlCon.Dispose()
                        sqlCon = Nothing

                        'Loop through all tables and all rows.
                        'All rows should have a "content" column. Depending upon the contents...
                        '...of the content column, a different header is presented.
                        For i As Integer = 0 To ds.Tables.Count - 1
                            For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
                                Dim title As String = Convert.ToString(ds.Tables(i).Rows(j).Item("content"))
                                Select Case title
                                    Case "SystemInformation"
                                        Writer.WritersHelper.WriteEntry("System Information", m_writers)
                                    Case "DatabaseInformation"
                                        Writer.WritersHelper.WriteEntry("Database Information", m_writers)
                                    Case "TableInfo"
                                        Writer.WritersHelper.WriteEntry("Table Information", m_writers)
                                End Select
                                For x As Integer = 0 To ds.Tables(i).Columns.Count - 1
                                    If ds.Tables(i).Columns(x).ColumnName.ToLower() <> "content" Then
                                        Writer.WritersHelper.WriteEntry(ds.Tables(i).Columns(x).ColumnName, SON(ds.Tables(i).Rows(j).Item(x)), m_writers)
                                    End If
                                Next
                                Writer.WritersHelper.WriteBlankEntry(m_writers)
                            Next
                        Next
                    Catch ex As Exception
                        'Error gathering info for this one.
                        Writer.WritersHelper.WriteEntryError("Error retrieving MSSQL information for database (" + ex.Message + ")", m_writers)
                    End Try
                Catch ex As Exception
                    'Error gathering info.
                    Writer.WritersHelper.WriteSectionError("Error retrieving MSSQL information (" + ex.Message + ")", m_writers)
                End Try
                'Write out a blank.
                Writer.WritersHelper.WriteBlank(m_writers)
            Next
        End If

    End Sub

    Public Sub Startup(ByVal cfgFile As String, ByVal writers As System.Collections.Generic.List(Of Writer.IWriter)) Implements ICollector.Startup

        'Get all writers.
        m_writers = writers
        'Read configuration file.
        ReadConfiguration(cfgFile)

    End Sub

    ''' <summary>
    ''' Read the configuration file.
    ''' </summary>
    ''' <param name="fName">File name with MSSQL collector configuration.</param>
    ''' <remarks></remarks>
    Protected Sub ReadConfiguration(ByVal fName As String)

        Const MSSQLSERVER_TABLE As String = "MSSQLServer"

        Try
            'Read in a dataset.
            Dim ds As New DataSet
            ds.ReadXml(fName)
            m_entries = New List(Of MSSQLEntry)

            'Parse all entries.
            For i As Integer = 0 To ds.Tables(MSSQLSERVER_TABLE).Rows.Count - 1
                With ds.Tables(MSSQLSERVER_TABLE).Rows(i)
                    m_entries.Add(New MSSQLEntry(Convert.ToString(.Item("DatabaseServer")), _
                                                 Convert.ToString(.Item("Instance")), _
                                                 Convert.ToString(.Item("Database")), _
                                                 Convert.ToBoolean(.Item("UseIntegratedSecurity")), _
                                                 Convert.ToString(.Item("UserID")), _
                                                 Convert.ToString(.Item("Password")), _
                                                 Convert.ToString(.Item("SQLQuery"))))
                End With
            Next

            ds.Dispose()
            ds = Nothing

            'Signal no error.
            m_error = ""
        Catch ex As Exception
            'Keep the error here.
            m_error = ex.ToString()
        End Try

    End Sub

    ''' <summary>
    ''' String-or-Null.
    ''' </summary>
    ''' <param name="o">Object to examine.</param>
    ''' <returns>The object's string representation unless it is null or dbnull.</returns>
    ''' <remarks></remarks>
    Protected Function SON(ByVal o As Object) As String
        If IsDBNull(o) OrElse (o Is Nothing) Then
            Return "<null>"
        Else
            Return Convert.ToString(o)
        End If
    End Function

End Class
